	USE master
	GO

	IF  EXISTS (
		SELECT name 
			FROM sys.databases 
			WHERE name = N'WorksManagerment'
	)
	DROP DATABASE WorksManagerment
	GO

	CREATE DATABASE WorksManagerment
	GO

	ALTER DATABASE WorksManagerment SET ANSI_NULL_DEFAULT OFF
	GO

	ALTER DATABASE WorksManagerment SET ANSI_NULLS OFF 
	GO

	ALTER DATABASE WorksManagerment SET ANSI_PADDING OFF 
	GO

	ALTER DATABASE WorksManagerment SET ANSI_WARNINGS OFF 
	GO

	ALTER DATABASE WorksManagerment SET ARITHABORT OFF 
	GO

	ALTER DATABASE WorksManagerment SET AUTO_CLOSE OFF 
	GO

	ALTER DATABASE WorksManagerment SET AUTO_CREATE_STATISTICS ON 
	GO

	ALTER DATABASE WorksManagerment SET AUTO_SHRINK OFF 
	GO

	ALTER DATABASE WorksManagerment SET AUTO_UPDATE_STATISTICS ON 
	GO

	ALTER DATABASE WorksManagerment SET CURSOR_CLOSE_ON_COMMIT OFF 
	GO

	ALTER DATABASE WorksManagerment SET CURSOR_DEFAULT  GLOBAL 
	GO

	ALTER DATABASE WorksManagerment SET CONCAT_NULL_YIELDS_NULL OFF 
	GO

	ALTER DATABASE WorksManagerment SET NUMERIC_ROUNDABORT OFF 
	GO

	ALTER DATABASE WorksManagerment SET QUOTED_IDENTIFIER OFF 
	GO

	ALTER DATABASE WorksManagerment SET RECURSIVE_TRIGGERS OFF 
	GO

	ALTER DATABASE WorksManagerment SET  ENABLE_BROKER 
	GO

	ALTER DATABASE WorksManagerment SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
	GO

	ALTER DATABASE WorksManagerment SET DATE_CORRELATION_OPTIMIZATION OFF 
	GO

	ALTER DATABASE WorksManagerment SET TRUSTWORTHY OFF 
	GO

	ALTER DATABASE WorksManagerment SET ALLOW_SNAPSHOT_ISOLATION OFF 
	GO

	ALTER DATABASE WorksManagerment SET PARAMETERIZATION SIMPLE 
	GO

	ALTER DATABASE WorksManagerment SET READ_COMMITTED_SNAPSHOT OFF 
	GO

	ALTER DATABASE WorksManagerment SET HONOR_BROKER_PRIORITY OFF 
	GO

	ALTER DATABASE WorksManagerment SET RECOVERY FULL 
	GO

	ALTER DATABASE WorksManagerment SET  MULTI_USER 
	GO

	ALTER DATABASE WorksManagerment SET PAGE_VERIFY CHECKSUM  
	GO

	ALTER DATABASE WorksManagerment SET DB_CHAINING OFF 
	GO





	USE WorksManagerment
	GO

	/*1- Role*/
	CREATE TABLE Role(
		RoleId int primary key identity,
		RoleName varchar(100)
	)
	GO

	/*2- Member*/
	CREATE TABLE Member(
		MemberId int primary key identity,
		Account varchar(32) unique,
		Password varchar(32),
		Email varchar(100),
		RoleId int,
	)
	GO

	/*3- City*/
	CREATE TABLE City(
		CityId int primary key identity,
		CityName varchar(50)
	)
	GO

	/*4- District*/
	CREATE TABLE District(
		DistrictId int primary key identity,
		DistrictName varchar(50),
		CityId int
	)
	GO

	/*5- School*/
	CREATE TABLE School(
		SchoolId int primary key identity,
		SchoolName varchar(50),
		CityId int
	)
	GO

	/*6- Type of work*/
	CREATE TABLE WorkType(
		TypeId int primary key identity,
		TypeName nvarchar(50),
		Description nvarchar(500)
	)
	GO

	/*7- Type of work status*/
	CREATE TABLE StatusType(
		StatusId int primary key identity,
		StatusName nvarchar(50),
		Description nvarchar(500)
	)
	GO

	/*8- Type of Recruiter rate status*/
	CREATE TABLE StatusRate(
		StatusId int primary key identity,
		StatusName nvarchar(50),
		Description nvarchar(500)
	)
	GO

	/*9- Position*/
	CREATE TABLE Position(
		PositionId int primary key identity,
		PositionName nvarchar(50),
		Description nvarchar(500)
	)
	GO

	/*10- CV*/
	CREATE TABLE CV(
		CVId int primary key identity,
		CVContent nvarchar(MAX),
		CVType bit, /*0-templete, 1-create*/
		MemberId int
	)
	GO

	/*11- Filter Word*/
	CREATE TABLE FilterWord(
		WordId int primary key identity,
		Word nvarchar(100),
		Description nvarchar(500)
	)
	GO

	/*12- Profile Candidate*/
	CREATE TABLE Candidate(
		CandidateId int primary key identity,
		MemberId int,
		CandidateName nvarchar(100),
		Birthday varchar(10),
		Literacy nvarchar(30),
		Specialize int, /*from reference data*/
		Phone varchar(12),
		Email varchar(100),
		Location int, /*from city*/
		Address nvarchar(200),
		School int, /*from school*/
		Status nvarchar(30), 
		Introduce nvarchar(MAX),
		DateCreated datetime,
		DateFixed datetime,
		IsActive bit
	)
	GO

	/*13- Profile Recruiter*/
	CREATE TABLE Recruiter(
		RecruiterId int primary key identity,
		MemberId int,
		CompanyName nvarchar(100),
		Phone varchar(12),
		Email varchar(100),
		Address nvarchar(200),
		Location int, /*from city*/
		Introduce nvarchar(MAX),
		NumberPosted int,
		Rate int,
		DateCreated datetime,
		DateFixed datetime,
		RateStatus int, /*from StatusRate*/
		IsActive bit
	)
	GO

	/*14- Work*/
	CREATE TABLE Work(
		WorkId int primary key identity,
		Title nvarchar(300),
		Location int, /*from city*/
		RecruiterId int,
		WorkType int, /*from work type*/
		Position int, /*from position*/
		Salary nvarchar(30),
		Description nvarchar(MAX),
		RequiredSex nvarchar(5),
		RequiredAge varchar(7),
		RequiredLiteracy varchar(30),
		RequiredNumber int,
		RequiredLaguage nvarchar(200), /*type:lang=[language& for many language required*/
		RequiredAnother nvarchar(1000),
		Phone varchar(12),
		Email varchar(100),
		Address nvarchar(200),
		DatePosted datetime,
		DateConfirmed datetime,
		DateExpired datetime,
		DateClose datetime,
		Rate int,
		ViewTime int,
		DateFixed datetime,
		FixBy int, /*memberid*/
		Status int /*from StatusType*/
	)
	GO

	/*15- MetaData*/
	CREATE TABLE MetaData(
		MetaCodeId int primary key identity,
		MetaCodeName nvarchar(100),
		MetaValue nvarchar(500)
	)
	GO
	/*
	/*16- Reference Data*/
	CREATE TABLE MetaData(
		MetaId int primary key identity,
		MetaCode int, /*from ReferenceCode*/
		MetaValue nvarchar(200)
	)
	GO*/

	/*/*17-- Member_RefData*/
	CREATE TABLE Ref_Member_MetaData(
		RefId int primary key identity,
		MemberId int, 
		MetaId int
	)
	GO*/

	/*/*18-- Member_Role*/
	CREATE TABLE Ref_Member_Role(
		MemberId int, 
		RoleId int
		primary key(MemberId, RoleId)
	)
	GO*/

	ALTER TABLE Candidate WITH CHECK ADD  CONSTRAINT FK_Candidate_Member FOREIGN KEY(MemberId) REFERENCES Member(MemberId)
	GO
	
	ALTER TABLE CV WITH CHECK ADD  CONSTRAINT FK_CV_Member FOREIGN KEY(MemberId) REFERENCES Member(MemberId)
	GO

	ALTER TABLE District WITH CHECK ADD  CONSTRAINT FK_District_City FOREIGN KEY(CityId) REFERENCES City(CityId)
	GO

	ALTER TABLE Recruiter WITH CHECK ADD  CONSTRAINT FK_Recruiter_Member FOREIGN KEY(MemberId) REFERENCES Member(MemberId)
	GO
	
	ALTER TABLE Recruiter WITH CHECK ADD  CONSTRAINT FK_Recruiter_StatusRate FOREIGN KEY(RateStatus) REFERENCES StatusRate(StatusId)
	GO
	
	/*ALTER TABLE MetaData WITH CHECK ADD  CONSTRAINT FK_MetaData_MetaCode FOREIGN KEY(MetaCode) REFERENCES MetaCode(MetaCodeId)
	GO

	ALTER TABLE Ref_Member_MetaData WITH CHECK ADD  CONSTRAINT FK_Ref_Member_MetaData_Member FOREIGN KEY(MemberId) REFERENCES Member(MemberId)
	GO
	
	ALTER TABLE Ref_Member_Role WITH CHECK ADD  CONSTRAINT FK_Ref_Member_Role_Member FOREIGN KEY(MemberId) REFERENCES Member(MemberId)
	GO

	ALTER TABLE Ref_Member_Role WITH CHECK ADD  CONSTRAINT FK_Ref_Member_Role_Role FOREIGN KEY(RoleId) REFERENCES Role(RoleId)
	GO*/

	ALTER TABLE Member WITH CHECK ADD CONSTRAINT FK_Member_Role FOREIGN KEY(RoleId) REFERENCES Role(RoleId)
	
	ALTER TABLE School WITH CHECK ADD  CONSTRAINT FK_School_City FOREIGN KEY(CityId) REFERENCES City(CityId)
	GO

	ALTER TABLE Work WITH CHECK ADD  CONSTRAINT FK_Work_Recruiter1 FOREIGN KEY(RecruiterId) REFERENCES Recruiter(RecruiterId)
	GO
	
	ALTER TABLE Work WITH CHECK ADD  CONSTRAINT FK_Work_Position FOREIGN KEY(Position) REFERENCES Position(PositionId)
	GO
	
	ALTER TABLE Work WITH CHECK ADD  CONSTRAINT FK_Work_StatusType FOREIGN KEY(Status) REFERENCES StatusType(StatusId)
	GO

	ALTER TABLE Work WITH CHECK ADD  CONSTRAINT FK_Work_WorkType FOREIGN KEY(WorkType) REFERENCES WorkType(TypeId)
	GO

	ALTER TABLE Work  WITH CHECK ADD  CONSTRAINT FK_Work_Member FOREIGN KEY(FixBy) REFERENCES Member (MemberId)
	GO

	USE master
	GO

	ALTER DATABASE WorksManagerment SET  READ_WRITE 
	GO
